package com.tianqu.tms.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.tianqu.tms.core.exception.TmsDaoException;
import com.tianqu.tms.dto.CarStyleDto;

public class CarStyleDaoImp extends TmsDao implements CarStyleDao {

	protected CarStyleDaoImp() throws TmsDaoException {
		super();
	}

	@Override
	public List<CarStyleDto> findAllCarStyleInfo() throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		CarStyleDto dto = null;
		List<CarStyleDto> codesList = new ArrayList<CarStyleDto>();
		
		try {
			conn = super.getConnection();
			
			StringBuffer sql = new StringBuffer();
			sql.append("SELECT 		");
			sql.append("S.ID AS ID        	,");
			sql.append("S.STYLENAME AS STYLENAME	,");
			sql.append("S.PINYIN AS PINYIN	  	,");
			sql.append("S.SEARCH 	AS SEARCH	,");
			sql.append("S.EXHAUST AS EXHAUST	,");
			sql.append("S.GEARBOX AS GEARBOX	,");
			sql.append("S.CARTYPE AS CARTYPE	,");
			sql.append("S.SID AS SID	 ,");
			sql.append("B.BRANDNAME AS CARNAME ");
			sql.append("FROM ");
			sql.append(TmsTableNames.TMS_CAR_STYLE);
			sql.append(" AS S,");
			sql.append(TmsTableNames.TMS_VEHICLE_BRAND);
            sql.append(" AS B ");
            sql.append(" WHERE ");
            sql.append(" S.SID=B.ID ");
			ps = conn.prepareStatement(sql.toString());
			// 执行sql
			rs = ps.executeQuery();

			while (rs.next()) {
				dto = new CarStyleDto();
				dto.setCarStyleId(rs.getString("ID"));
				dto.setCarStyleName(rs.getString("STYLENAME"));
				dto.setCarStylePinYin(rs.getString("PINYIN"));
				dto.setSearch(rs.getString("SEARCH"));
				dto.setExhaust(rs.getFloat("EXHAUST"));
				dto.setGearbox(rs.getInt("GEARBOX"));
				dto.setCarType(rs.getInt("CARTYPE"));
				dto.setBrandId(rs.getString("SID"));
				dto.setCarStyleRealName(rs.getString("CARNAME"));
				codesList.add(dto);
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			close(rs,ps);
		}
		return codesList;
	}
	public CarStyleDto findCarStyleInfo(String modelIdOther) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		CarStyleDto dto = new CarStyleDto();		
		try {
			conn = super.getConnection();			
			StringBuffer sql = new StringBuffer();
			sql.append("SELECT ");
			sql.append("S.ID AS ID ,");
			sql.append("S.SID AS SID ,");
			sql.append("S.STYLENAME AS STYLENAME ,");		
			sql.append("B.VENDORID AS VENDORID  ");
			sql.append("FROM ");
			sql.append(TmsTableNames.TMS_CAR_STYLE);
			sql.append(" AS S ");
			sql.append(" LEFT JOIN  ");
			sql.append(TmsTableNames.TMS_VEHICLE_BRAND);
            sql.append(" AS B ");
            sql.append(" ON S.SID = B.ID ");
            sql.append(" WHERE ");
            sql.append(" MODELIDOTHER = ? ");
            
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, modelIdOther);
			
			// 执行sql
			rs = ps.executeQuery();

			while (rs.next()) {				
				dto.setCarStyleId(rs.getString("ID"));
				dto.setBrandId(rs.getString("SID"));
				dto.setCarStyleName(rs.getString("STYLENAME"));				
				dto.setVendorId(rs.getString("VENDORID"));
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			close(rs,ps);
		}
		return dto;
	}
	
	public CarStyleDto getCarStyleInfo(String id) throws TmsDaoException{
		PreparedStatement ps = null;
		ResultSet rs = null;
		CarStyleDto dto = new CarStyleDto();		
		try {
			conn = super.getConnection();			
			StringBuffer sql = new StringBuffer();
			sql.append("SELECT ");
			sql.append("ID,");
			sql.append("STYLENAME ");		
			sql.append("FROM ");
			sql.append(TmsTableNames.TMS_CAR_STYLE);
            sql.append(" WHERE ");
            sql.append(" ID = ? ");
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, id);
			rs = ps.executeQuery();
			while (rs.next()) {				
				dto.setCarStyleId(rs.getString("ID"));
				dto.setCarStyleName(rs.getString("STYLENAME"));				
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			close(rs,ps);
		}
		return dto;
	
		
	}

    @Override
    public CarStyleDto findCarStyleInfoByModelId(String modelId) throws TmsDaoException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        CarStyleDto dto = new CarStyleDto();        
        try {
            conn = super.getConnection();           
            StringBuffer sql = new StringBuffer();
            sql.append("SELECT ");
            sql.append("S.ID AS ID ,");
            sql.append("S.SID AS SID ,");
            sql.append("S.STYLENAME AS STYLENAME ,");       
            sql.append("B.VENDORID AS VENDORID  ");
            sql.append("FROM ");
            sql.append(TmsTableNames.TMS_CAR_STYLE);
            sql.append(" AS S ");
            sql.append(" LEFT JOIN  ");
            sql.append(TmsTableNames.TMS_VEHICLE_BRAND);
            sql.append(" AS B ");
            sql.append(" ON S.SID = B.ID ");
            sql.append(" WHERE ");
            sql.append(" S.ID = ? ");
            
            ps = conn.prepareStatement(sql.toString());
            ps.setString(1, modelId);
            
            // 执行sql
            rs = ps.executeQuery();

            while (rs.next()) {             
                dto.setCarStyleId(rs.getString("ID"));
                dto.setBrandId(rs.getString("SID"));
                dto.setCarStyleName(rs.getString("STYLENAME"));             
                dto.setVendorId(rs.getString("VENDORID"));
            }
        } catch (SQLException e) {
            throw new TmsDaoException(e.toString());
            
        } finally {
            close(rs,ps);
        }
        return dto;
    }
}
